package fy.uap.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.logging.LogFactory;

import com.seeyon.ctp.util.UUIDLong;
import com.soa.eis.adapter.framework.message.IMsgObject;
import com.soa.eis.adapter.framework.message.impl.GroupRecord;
import com.soa.eis.adapter.framework.message.impl.MsgObject;
import com.soa.eis.adapter.framework.requester.IServiceRequester;
import com.soa.eis.adapter.framework.requester.impl.BaseServiceRequester;

import fy.config.FyConfig;
import fy.mdm.dao.MaterialRequestNoTimerTask;
import fy.uap.bean.NewFinanceSupplierBean;
import fy.util.DBUtil;
import fy.util.MyUtil;

public class NewFinanceSupplierDao {

    private static org.apache.commons.logging.Log log = LogFactory
            .getLog(NewFinanceSupplierDao.class);

    private String memberCode;

    private List<NewFinanceSupplierBean> beans;

    // private List<String> pk_vendorList;
    // private String applicationTime;
    private final static String uapUserID = "0001AA10000000009KX7";

    private String number;

    private final static String insertSupplier = "insert into fy_supplier_vendor ( pk_vendor, cvenname, cvencode, cvendefine1 ) select ?, ?, ?, ? from dual where not exists ( select * from fy_supplier_vendor v where v.cvencode = ? or v.cvenname = ?)";
    private final static String insertLog = "insert into fy_supplier_vendor_history(pk_venhis, pk_vendor, pk_corp, pk_invclass, changetype, changememo, changevouch, changecode, changer, ts, changedate,creater) select ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? from dual where not exists ( select * from fy_supplier_vendor_invclass vi where vi.pk_corp = ? and vi.pk_vendor = ? and (vi.dr is not null or vi.dr = 0))";
    private final static String insertVendorInvcalss = " insert into fy_supplier_vendor_invclass (pk_veninv, pk_corp, pk_invclass, cvendorclass, ts, pk_vendor, dvendevdate, changeTrace,creater) select  ?, ?, ?, ?, ?, ?, ?, ?, ? from dual where not exists ( select * from fy_supplier_vendor_invclass vi where vi.pk_corp = ? and vi.pk_vendor = ? and (vi.dr is not null or vi.dr = 0))";

    private final static String queryInvClass = "select i.pk_invclass from fy_supplier_invclass i where i.cinvclasscode = 'NONE'";

    private static String pk_invclass;

    // private static final String ERPDELETESQL =
    // "delete from apps.FYG_PO_UAP_VENDORS@"
    // + OADBConf.UAP_DBLINK_ERP +
    // " i where i.VENDORS_NUM = ? and i.UAPLB = 'NONE'  and i.ZT<>'客户' ";
    // private static final String ERPINSERTSQL =
    // "insert into apps.FYG_PO_UAP_VENDORS@"
    // + OADBConf.UAP_DBLINK_ERP
    // +
    // "(VENDORS_NUM, VENDORS_NAME, SYB, UAPLB, ZT, disable_date,flag) select v.cvencode,v.cvenname, c.unitname, i.cinvclasscode,         decode(vi.cvendorclass,'1','合格','2','潜在','3','黑名单',vi.cvendorclass,vi.cvendorclass),         to_date(vi.denddate,'YYYY-MM-DD'),'Y'    from fy_supplier_vendor_invclass vi    left join fy_supplier_vendor v on vi.pk_vendor = v.pk_vendor    left join bd_corp c on vi.pk_corp = c.pk_corp    left join fy_supplier_invclass i on vi.pk_invclass = i.pk_invclass   where v.cvencode = ? and i.cinvclasscode='NONE' and (vi.dr is null or vi.dr = 0) and  vi.cvendorclass <>'3' and (vi.denddate is null or to_date(vi.denddate,'yyyy-mm-dd') > to_date( to_char(sysdate, 'yyyy-mm-dd'), 'yyyy-mm-dd' ))";
    private static final String ERPUADATE = "update apps.FYG_PO_UAP_VENDORS@"
            + FyConfig.getERP_DB_CONFIG().getDblink()
            + " av set (av.vendors_name, av.flag,av.zt,av.disable_date) = (select v.cvenname cvenname,      decode(sign(to_date(vi.denddate, 'YYYY-MM-DD') - sysdate),   -1,   'N',   decode(vi.cvendorclass, '3', 'N', 'Y')) flag   ,decode(sign(to_date(vi.denddate, 'YYYY-MM-DD') - sysdate),-1,'停用',   decode(vi.cvendorclass,          '1',          '合格',          '2',          '潜在',          '3',          '黑名单',          vi.cvendorclass)) zt,      to_date(vi.denddate, 'YYYY-MM-DD') disable_date from fy_supplier_vendor_invclass vi left join fy_supplier_vendor v on vi.pk_vendor = v.pk_vendor left join bd_corp c on vi.pk_corp = c.pk_corp left join fy_supplier_invclass i on vi.pk_invclass =       i.pk_invclass          where  v.cvencode = ? and (vi.dr is null or vi.dr = 0) and av.vendors_num =v.cvencode and av.syb=c.unitname and av.uaplb = i.cinvclasscode )  where av.vendors_num =  ? and exists (select * from fy_supplier_vendor_invclass vi left join fy_supplier_vendor v on vi.pk_vendor = v.pk_vendor left join bd_corp c on vi.pk_corp = c.pk_corp left join fy_supplier_invclass i on vi.pk_invclass =       i.pk_invclass          where  v.cvencode = ? and (vi.dr is null or vi.dr = 0) and av.vendors_num =v.cvencode and av.syb=c.unitname and av.uaplb = i.cinvclasscode )";

    private static final String ERPINSERTSQL = "insert into apps.FYG_PO_UAP_VENDORS@"
            + FyConfig.getERP_DB_CONFIG().getDblink()
            + "(VENDORS_NUM, VENDORS_NAME, SYB, UAPLB, ZT, disable_date,flag) select * from (select v.cvencode cvencode,  v.cvenname cvenname,  c.unitname unitname,  i.cinvclasscode cinvclasscode,  decode(sign(to_date(vi.denddate, 'YYYY-MM-DD') - sysdate),-1,'停用',decode(vi.cvendorclass,'1','合格',  '2',  '潜在',  '3',  '黑名单',  vi.cvendorclass)) zt,  to_date(vi.denddate, 'YYYY-MM-DD') disable_date,  decode(sign(to_date(vi.denddate, 'YYYY-MM-DD') - sysdate),-1,'N',decode(vi.cvendorclass,  '3',  'N',  'Y')) flag    from fy_supplier_vendor_invclass vi    left join fy_supplier_vendor v on vi.pk_vendor = v.pk_vendor    left join bd_corp c on vi.pk_corp = c.pk_corp    left join fy_supplier_invclass i on vi.pk_invclass = i.pk_invclass where v.cvencode=? and (vi.dr is null or vi.dr = 0) ) t where not exists ( select *    from apps.FYG_PO_UAP_VENDORS@"
            + FyConfig.getERP_DB_CONFIG().getDblink()
            + " e where t.cvencode = e.VENDORS_NUM and e.SYB = t.unitname and e.UAPLB=t.cinvclasscode and t.zt = e.zt and (t.disable_date is null and e.disable_date is null or t.disable_date = e.disable_date) ) ";

    // public void setCorpList(List<String> corpList) {
    // this.corpList = corpList;
    // }
    public void setNumber(String number) {
        this.number = number;
    }

    public void setMemberCode(String memberCode) {
        this.memberCode = memberCode;
    }

    public void setBeans(List<NewFinanceSupplierBean> beans) {
        this.beans = beans;
    }

    public void setApplicationTime(String applicationTime) {
        // this.applicationTime = applicationTime;
    }
	//写入MDM数据库
	public boolean getStateReturn() {
//		System.err.println("123");
		try {
			
			for (int i = 0, size = beans.size(); i < size; i++) {
				NewFinanceSupplierBean bean = beans.get(i);
				IServiceRequester Requester = BaseServiceRequester.getInstance();
				List<GroupRecord> grs1 = new ArrayList<GroupRecord>();//多层用
				// 定义请求报文对象
				IMsgObject reqMo = new MsgObject(IMsgObject.MOType.initSR);
				String SourceSysID = "06004"; // 请求方(发布方)
				String ServiceID = "06001000000039";// 响应方(发布方)
				// 获取系统时间
				Date date = new Date();
				SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
				String format = simpleDateFormat.format(date);
				// 对请求报文对象进行设置
				reqMo.setSourceSysID(SourceSysID); // 设置报文 请求方系统号
				reqMo.setSerialNO(format.substring(0, 8) + SourceSysID
						+ String.format("%06d", ++MaterialRequestNoTimerTask.requestNo));// 设置报文流水号,第三段表示今天发送的第几的一次报文
				reqMo.setServiceID(ServiceID); // 设置报文 提供方服务号
				reqMo.setServiceDateTime(format); // 设置报文 日期时间
				
				reqMo.setReqValue("BOID","MDM_EQUIPMENT_F" );
				GroupRecord gr1 = new GroupRecord();
				gr1.setName("ROW");
				gr1.setFieldValue("MDM_CODE",bean.getSupplierCode());//供应商编码
				gr1.setFieldValue("Name", bean.getSupplierName());//供应商名称
				gr1.setFieldValue("UpdateTime", format);//修改时间
				grs1.add(gr1);
				reqMo.setReqGroupRecord(grs1);
//				System.err.println(grs1);
				log.info("reqMo:"+reqMo);
//				System.err.println(reqMo);
				IMsgObject respMo = null;
				respMo = Requester.execute(reqMo);//发布报文
//				System.err.println(respMo);
				log.info("respMo:"+respMo);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return true;
		
	}
    public boolean save() {
        boolean success = false;

        Connection conn = null;
        PreparedStatement pst = null;
        PreparedStatement pst1 = null;
        ResultSet rs = null;

        try {

            conn = DBUtil.connect(FyConfig.getUAP_DB_CONFIG());
            // insert into fy_supplier_vendor ( pk_vendor, cvenname, cvencode,
            // cvendefine1 ) select ?, ?, ?, ? from dual where not exists (
            // select * from fy_supplier_vendor v where v.cvencode = ? or
            // v.cvenname = ?
            //
            conn.setAutoCommit(false);
            pst = conn.prepareStatement(insertSupplier);
            addSupplierBatch(pst);
            pst.executeBatch();

            DBUtil.close(pst);

            /**
             * 查询是否都已经把供应商信息写入数据
             */
            pst = conn.prepareStatement(getQuerySupplierSql());
            for (int i = 0, size = beans.size(); i < size; i++) {
                NewFinanceSupplierBean bean = beans.get(i);
                pst.setString(2 * i + 1, bean.getSupplierCode());
                pst.setString(2 * i + 2, bean.getSupplierName());
                bean = null;
            }
            rs = pst.executeQuery();
            // pk_vendorList = new ArrayList<String>();
            // while (rs.next()) {
            // pk_vendorList.add(rs.getString(1));
            // }
            while (rs.next()) {
                for (int i = 0, size = beans.size(); i < size; i++) {
                    if (beans.get(i).getSupplierCode().equals(rs.getString(2))) {
                        beans.get(i).setPk_vendor(rs.getString(1));
                    }
                }
            }

            DBUtil.close(rs, pst);

            if (pk_invclass == null) {
                pst = conn.prepareStatement(queryInvClass);
                rs = pst.executeQuery();
                if (rs.next()) {
                    pk_invclass = rs.getString(1);
                }
                DBUtil.close(rs, pst);
            }
            if (pk_invclass != null) {

                // 日志
                pst1 = conn.prepareStatement(insertLog);
                addLogBacth(pst1, pk_invclass);
                pst1.executeBatch();

                // 供货范围
                pst = conn.prepareStatement(insertVendorInvcalss);
                addVedorInvclassBatch(pst, pk_invclass);

                pst.executeBatch();

                DBUtil.close(rs, pst);

                // ERP更新
                pst = conn.prepareStatement(ERPUADATE);
                for (int i = 0, size = beans.size(); i < size; i++) {
                    NewFinanceSupplierBean bean = beans.get(i);
                    pst.setString(1, bean.getSupplierCode());
                    pst.setString(2, bean.getSupplierCode());
                    pst.setString(3, bean.getSupplierCode());
                    pst.addBatch();
                    bean = null;
                }
                pst.executeBatch();
                DBUtil.close(pst);

                // ERP插入
                List<String> tempCodeList = new ArrayList<String>();
                pst = conn.prepareStatement(ERPINSERTSQL);
                for (int i = 0, size = beans.size(); i < size; i++) {
                    NewFinanceSupplierBean bean = beans.get(i);
                    if (tempCodeList.indexOf(bean.getSupplierCode()) == -1) {
                        pst.setString(1, bean.getSupplierCode());
                        pst.addBatch();
                        tempCodeList.add(bean.getSupplierCode());
                    }
                    bean = null;
                }
                tempCodeList = null;

                pst.executeBatch();

                conn.commit();

                success = true;
                // StringBuilder sb = new StringBuilder().append(queryCorpName);
                //
                // for (int i = 1, length = corpList.size(); i < length; i++) {
                // sb.append(" or c.pk_corp = ? ");
                // }
                //
                // pst = conn.prepareStatement(sb.toString());
                // for (int i = 0, length = corpList.size(); i < length; i++) {
                // pst.setString(i + 1, corpList.get(i));
                // }
                //
                // rs = pst.executeQuery();
                //
                // while (rs.next()) {
                // corpnameMap.put(rs.getString(2), rs.getString(1));
                // success = true;
                // }

            } else {
                log.error("未获取到财务供应商供货范围，无法数据写入UAP系统，异常表单为《供应商编码新增申请单》，单据编号："
                        + number);
            }
            if (!success) {
                conn.rollback();
            }

        } catch (Exception e) {
            success = false;
            DBUtil.rollback(conn);
            e.printStackTrace();
            log.error("《供应商编码新增申请单》导入UPA异常，单据编号：" + number, e);
        } finally {
            DBUtil.setAutoCommit(conn, true);
            DBUtil.close(pst1);
            DBUtil.close(rs, pst, conn);
        }

        return success;
//    	return true;
    }

    /**
     * 写入到ERP数据库
     */
    public boolean writeToErp() {
        return true;
        // boolean writeToErpSuccess = false;
        // Connection conn = null;
        // PreparedStatement pst = null;
        // try {
        //
        // // FileOutputStream fos = new FileOutputStream("E:/1111");
        // // fos.write(providerImportFormBean.printFields().getBytes());
        // // fos.flush();
        // // fos.close();
        //
        // conn = DBUtil.connect(ERP_OAConstant.ERPDriverClassName,
        // ERP_OAConstant.ERPURL, ERP_OAConstant.ERPUserName,
        // ERP_OAConstant.ERPPassword);
        //
        // conn.setAutoCommit(false);
        //
        // pst = conn
        // .prepareStatement("insert into apps.FYG_PO_UAP_VENDORS (VENDORS_NUM, VENDORS_NAME, SYB, UAPLB, ZT) select  ?, ?, ?, ?, ?  from dual where not exists( select * from apps.FYG_PO_UAP_VENDORS a where a.VENDORS_NUM=? and a.SYB=?)");
        // // insert into apps.FYG_PO_UAP_VENDORS (VENDORS_NUM 供应商编号,
        // // VENDORS_NAME 供应商名称, SYB 事业部, UAPLB UAP类别, ZT 状态) select ?, ?,
        // // ?,
        // // ?, ?, ?, ?, ?, ? from dual where not exists ( select * from
        // // apps.FYG_PO_UAP_VENDORS f where f.VENDORS_NUM=? and f.SY=?
        // // and
        // // f.UAPLB=?)");
        // NewFinanceSupplierBean range = null;
        // for (int i = 0, size = beans.size(); i < size; i++) {
        // range = beans.get(i);
        // String corpname = corpnameMap.get(range.getCorpID());
        // pst.setString(1, range.getSupplierCode());
        // pst.setString(2, range.getSupplierName());
        // pst.setString(3, corpname);
        // pst.setString(4, "NONE");
        // pst.setString(5, "99");
        // pst.setString(6, range.getSupplierCode());
        // pst.setString(7, corpname);
        // // pst.setString(6, range.getProviderCode());
        // // pst.setString(7, range.getCorpName());
        // // pst.setString(8, range.getInvclassCode());
        // pst.addBatch();
        // range = null;
        // }
        // pst.executeBatch();
        //
        // conn.commit();
        //
        // writeToErpSuccess = true;
        //
        // } catch (Exception e) {
        // DBUtil.rollback(conn);
        // DBUtil.setAutoCommit(conn, true);
        // e.printStackTrace();
        // log.error("《供应商编码新增申请单》写入ERP数据库异常,单据编号： " + number, e);
        // writeToErpSuccess = false;
        // } finally {
        // DBUtil.setAutoCommit(conn, true);
        // DBUtil.close(pst, conn);
        // }
        // return writeToErpSuccess;
    }

    private void addSupplierBatch(PreparedStatement pst) throws SQLException {
        List<String> codes = new ArrayList<String>();
        for (int i = 0, size = beans.size(); i < size; i++) {
            NewFinanceSupplierBean bean = beans.get(i);
            if (codes.indexOf(bean.getSupplierCode()) == -1) {
                codes.add(bean.getSupplierCode());
                bean.setPk_vendor(String
                        .valueOf(UUIDLong
                                .longUUID()));
                pst.setString(1, bean.getPk_vendor());
                pst.setString(2, bean.getSupplierName());
                pst.setString(3, bean.getSupplierCode());
                pst.setString(4, "financial");

                pst.setString(5, bean.getSupplierCode());
                pst.setString(6, bean.getSupplierName());

                pst.addBatch();

                bean = null;
            }
        }
    }

    private String getQuerySupplierSql() {
        StringBuilder sb = new StringBuilder()
                .append("select v.pk_vendor, v.cvencode from fy_supplier_vendor v where (v.dr is null or v.dr = 0 ) and (v.cvendefine1 = 'financial' or substr(v.cvencode,0,1) = 'N') and ( v.cvencode = ? and v.cvenname = ? ");
        for (int i = 1, size = beans.size(); i < size; i++) {
            sb.append(" or v.cvencode = ? and v.cvenname = ? ");
        }
        sb.append(")");
        return sb.toString();
    }

    private void addVedorInvclassBatch(PreparedStatement pst, String pk_invClass)
            throws SQLException {
        // insert into fy_supplier_vendor_invclass (pk_veninv,
        // pk_corp,
        // pk_invclass, cvendorclass, ts, pk_vendor,
        // dvendevdate,
        // changeTrace,creater) values ( ?, ?, ?, ?, ?, ?, ?, ?,
        // ?)
        for (int i = 0, size = beans.size(); i < size; i++) {
            NewFinanceSupplierBean bean = beans.get(i);
            pst.setString(1, String
                    .valueOf(UUIDLong.longUUID()));
            pst.setString(2, bean.getCorpID());
            pst.setString(3, pk_invClass);
            pst.setString(4, "99");
            pst.setString(5, MyUtil.getNowStr());
            pst.setString(6, bean.getPk_vendor());
            pst.setString(7, MyUtil.getTodayDateStr());
            pst.setString(8, "");
            pst.setString(9, memberCode);
            pst.setString(10, bean.getCorpID());
            pst.setString(11, bean.getPk_vendor());
            pst.addBatch();
            bean = null;
        }
    }

    private void addLogBacth(PreparedStatement pst1, String pk_invclass)
            throws SQLException {
        for (int i = 0, size = beans.size(); i < size; i++) {
            NewFinanceSupplierBean bean = beans.get(i);
            pst1.setString(1, String
                    .valueOf(UUIDLong.longUUID()));
            pst1.setString(2, bean.getPk_vendor());
            pst1.setString(3, bean.getCorpID());
            pst1.setString(4, pk_invclass);
            pst1.setString(5, "新增");
            pst1.setString(6, "");
            pst1.setString(7, "《供应商编码新增申请单》");
            pst1.setString(8, number);
            pst1.setString(9, uapUserID);
            pst1.setString(10, MyUtil.getNowStr());
            pst1.setString(11, MyUtil.getTodayDateStr());
            pst1.setString(12, memberCode);
            pst1.setString(13, bean.getCorpID());
            pst1.setString(14, bean.getPk_vendor());
            pst1.addBatch();
        }
    }

}
